package com.wei.czz.common.easyexcel.handler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 *
 * @author wyw
 * date: 2024-12-27 14:53:07
 * className: CzzWriteHandler
 * version: 1.0
 * description:
 */
public class CzzWriteHandler implements SheetWriteHandler {

    /**
     * 隐藏sheet名称前缀
     */
    private static final String HIDE_SHEET_NAME_PREFIX = "hide_sheet_";

    /**
     * 隐藏sheet开始下标
     */
    private final Integer hideSheetStartIndex;

    /**
     * 开始数据行数
     */
    private final Integer firstRow;

    /**
     * 结束数据行数
     */
    private final Integer lastRow;

    /**
     * 字典枚举映射
     * key：  字段所在列
     * value：字段枚举列表
     */
    private final Map<Integer, List<String>> enumMap;

    public CzzWriteHandler(Map<Integer, List<String>> enumMap) {
        this(1, 1, 1048575, enumMap);
    }

    public CzzWriteHandler(Integer lastRow, Map<Integer, List<String>> enumMap) {
        this(1, 1, lastRow, enumMap);
    }

    public CzzWriteHandler(Integer firstRow, Integer lastRow, Map<Integer, List<String>> enumMap) {
        this(1, firstRow, lastRow, enumMap);
    }

    public CzzWriteHandler(Integer hideSheetStartIndex, Integer firstRow, Integer lastRow, Map<Integer, List<String>> enumMap) {
        this.hideSheetStartIndex = hideSheetStartIndex;
        this.firstRow = firstRow;
        this.lastRow = lastRow;
        this.enumMap = enumMap;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取第一个sheet页
        Sheet sheet = writeSheetHolder.getCachedSheet();
        // 获取sheet页的数据校验对象
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 获取工作薄对象，用于创建存放枚举下拉数据的sheet数据页
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 迭代索引，用于创建存放枚举下拉数据的sheet数据页名
        int index = hideSheetStartIndex;
        for (Map.Entry<Integer, List<String>> entry : enumMap.entrySet()) {
            // 设置存放枚举下拉数据的sheet，并把sheet隐藏掉，这样用户交互更友好
            String dictSheetName = HIDE_SHEET_NAME_PREFIX + index;
            // 创建一个sheet对象
            Sheet dictSheet = workbook.createSheet(dictSheetName);
            // 隐藏sheet
            workbook.setSheetHidden(index++, true);

            /*
                设置下拉列表覆盖的行数与列数
                注意
                  1、Excel的索引是从0开始的，如果第一是标题行的化，firstRow值应该大于0，表示从第1行开始
                  2、最后一行的索引是1048575，不能超过这个值，否则可能导致设置失效
             */
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, entry.getKey(), entry.getKey());
            List<String> enumValueList = entry.getValue();
            int rowLen = enumValueList.size();
            for (int i = 0; i < rowLen; i++) {
                // 向sheet的第i行、第0列设置枚举值
                dictSheet.createRow(i).createCell(0).setCellValue(enumValueList.get(i));
            }
            // 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的
            String refers = dictSheetName + "!$A$1:$A$" + enumValueList.size();
            Name name = workbook.createName();
            name.setNameName(dictSheetName);
            // 将关联公式和sheet页做关联
            name.setRefersToFormula(refers);

            // 将上面设置好的枚举下拉列表sheet页和目标sheet关联起来
            DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            sheet.addValidationData(dataValidation);
        }
    }

}
